package com.skeeey.aim.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.skeeey.aim.model.Dealer;

public class VINStatistic {
	private String year = "2013";
	private int quarter = -1;
	private Workbook workbook = null;
	private int count0 = 1;
	private int row0Index = 2;
	private int count1 = 1;
	private int row1Index = 1;
	private int count2 = 1;
	private int row2Index = 1;
	private Map<Dealer,String> customerCodes = new HashMap<Dealer, String>();
	
	public void doStatistic(String year, int quarter, List<Dealer> dealers) throws IOException {
		this.quarter = quarter;
		this.year = year;
		Map<Dealer, File> files = getValidFiles(this.year, this.quarter, dealers);
		if(!files.isEmpty()) {
			File qFile = new File(AIMUtil.getSystemConfig("execl.v.path"));
			InputStream in = null;
			try {
				in = new FileInputStream(qFile);
				workbook = new HSSFWorkbook(in);
				for(Entry<Dealer, File> e : files.entrySet()) {
					if(e.getValue().exists() && e.getValue().isFile()) {
						Map<Integer,List<List<String>>> records = getRecords(e.getValue());
						setRecords(e.getKey(), records);
					}
				}
				createVSFile();
			} catch(Exception e) {
				e.printStackTrace();
			} finally {
				if (in != null) {
					try {
						in.close();
					} catch (IOException e) {
						// do nothing
					}
				}
			}
		} else {
			new IllegalArgumentException("后台无相应数据！！");
		}
	}
	
	private Map<Dealer, File> getValidFiles(String year, int quarter, List<Dealer> dealers) {
		Map<Dealer, File> files = new HashMap<Dealer, File>();
		for(Dealer d : dealers) {
			File path = new File(AIMUtil.getSystemConfig("data.storage.path") + File.separator 
					+ d.getRssc() + File.separator 
					+ d.getProvince() + File.separator 
					+ d.getCity() + File.separator 
					+ d.getShortName());
			if(path.exists() && path.isDirectory()) {
				File[] subFiles = path.listFiles();
				int count = 0;
				for(File subPath : subFiles) {
					if(subPath.isDirectory()) {
						String date = subPath.getName();
						if(QuarterStatistic.isSuitableDate(date, this.year, this.quarter)) {
							if(count > 0) {
								throw new IllegalArgumentException("[" + d.getShortName() + "]有重复统计的数据" + "，请先处理");
							}
							File file = new File(path + File.separator + date + File.separator +
									"上海大众库存信息整理表" + d.getNetCode() + "-" + d.getShortName() + "-" + date + ".xls");
							files.put(d, file);
							File sFile = new File(path + File.separator + date + File.separator + 
									"上海大众-" + d.getNetCode() + "-" + d.getShortName() + "-原始清单.xls");
							customerCodes.put(d, getCustomerCode(sFile));
							count++;
						}
					}
				}
			}
		}
		return files;
	}
	
	private String getCustomerCode(File file) {
		String code = null;
		if(file.exists() && file.isFile()) {
			InputStream in = null;
			try {
				in = new FileInputStream(file);
				Workbook workbook = null;
				try {
					workbook = new HSSFWorkbook(in);
				} catch(Exception e) {
					if (in != null) {
						try {
							in.close();
						} catch (IOException ei) {
							// do nothing
						}
					}
					in = new FileInputStream(file);
					workbook = new XSSFWorkbook(in);
				}
				Sheet sheet = workbook.getSheet("在库");
				Row row0 = sheet.getRow(0);
				int codeIndex = -1;
				for(int i = 0; i< 3; i++) {
					if("客户代码".equals(AIMUtil.getValueFrom(row0.getCell(i)))) {
						codeIndex = i;
						break;
					}
				}
				
				if(codeIndex > -1) {
					code = AIMUtil.getValueFrom(sheet.getRow(1).getCell(codeIndex));
				}
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			} finally {
				if (in != null) {
					try {
						in.close();
					} catch (IOException e) {
						// do nothing
					}
				}
			}
			
		}
		return code;
	}
	
	private Map<Integer,List<List<String>>> getRecords(File file) {
		Map<Integer,List<List<String>>> records = new HashMap<Integer, List<List<String>>>();
		InputStream in = null;
		try {
			in = new FileInputStream(file);
			Workbook workbook = new HSSFWorkbook(in);
			records.put(Integer.valueOf(0), getRecordList(workbook.getSheet("在库车辆-状态一"), 5, 3, 15));
			records.put(Integer.valueOf(1), getRecordList(workbook.getSheet("在库车辆-状态二"), 6, 3, 16));
			records.put(Integer.valueOf(2), getRecordList(workbook.getSheet("虚拟未查到"), 4, 3, 19));
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (in != null) {
				try {
					in.close();
				} catch (IOException e) {
					// do nothing
				}
			}
		}
		return records;
	}
	
	private List<List<String>> getRecordList(Sheet sheet, int rowStartIndex, int cellStartIndex, int cellSize) throws UnsupportedEncodingException {
		List<List<String>> rowList = new ArrayList<List<String>>();
		for(int i = rowStartIndex; i < sheet.getLastRowNum(); i++) {
			Row row = sheet.getRow(i);
			String firstCellValue = AIMUtil.getValueFrom(row.getCell(1));
			if(firstCellValue == null || firstCellValue.length() == 0 || !AIMUtil.isNumeric(firstCellValue)) {
				break;
			}
			List<String> rowValues = new ArrayList<String>();
			for(int j = cellStartIndex; j < cellSize; j++) {
				String cellValue = AIMUtil.getValueFrom(row.getCell(j));
				rowValues.add(cellValue);
			}
			rowList.add(rowValues);
		}
		return rowList;
	}
	
	private void setRecords(Dealer dealer, Map<Integer,List<List<String>>> records) {
			Sheet sheet0 = workbook.getSheet("VIN码汇总");
			List<List<String>> rows0 = records.get(Integer.valueOf(0));
			for(List<String> rowValues : rows0) {
				setRecords0(sheet0, dealer, rowValues);
			}
			Sheet sheet1 = workbook.getSheet("账面未确认车辆列表");
			List<List<String>> rows1 = records.get(Integer.valueOf(1));
			for(List<String> rowValues : rows1) {
				setRecords1(sheet1, dealer, rowValues);
			}
			Sheet sheet2 = workbook.getSheet("已零售出库但未开发票车辆-未确认在库车辆列表");
			List<List<String>> rows2 = records.get(Integer.valueOf(2));
			for(List<String> rowValues : rows2) {
				setRecords2(sheet2, dealer, rowValues);
			}
	}
	
	private Cell getCell(Row row, int index) {
		Cell c = row.getCell(index);
		if(c == null) {
			c = row.createCell(index);
		}
		return c;
	}
	
	private void setRecords0(Sheet sheet, Dealer dealer, List<String> records) {
		if(!records.isEmpty()) {
			Row row = sheet.getRow(row0Index);
			getCell(row, 0).setCellValue(count0);
			getCell(row, 1).setCellValue(dealer.getRssc());
			getCell(row, 2).setCellValue(dealer.getProvince());
			getCell(row, 3).setCellValue(dealer.getCity());
			getCell(row, 4).setCellValue(dealer.getNetCode());
			if(customerCodes.get(dealer) != null) {
				getCell(row, 5).setCellValue(customerCodes.get(dealer)); // 开票代码
			}
			getCell(row, 6).setCellValue(dealer.getName());
			getCell(row, 7).setCellValue(dealer.getShortName());
			getCell(row, 8).setCellValue(records.get(0)); // 开单日期
			getCell(row, 9).setCellValue(records.get(1)); // VIN码
			getCell(row, 10).setCellValue(records.get(2)); // 发动机号码
			getCell(row, 11).setCellValue(records.get(3)); // 车辆代码
			getCell(row, 12).setCellValue(records.get(4)); // 车辆名称
			getCell(row, 13).setCellValue(records.get(8)); // 车型大类
			getCell(row, 14).setCellValue(records.get(5)); // 颜色代码
			getCell(row, 15).setCellValue(records.get(6)); // 颜色名称
			getCell(row, 16).setCellValue(records.get(7)); // 存放地点
			getCell(row, 17).setCellValue("账面-确认在库"); // 现场检查状态
			getCell(row, 23).setCellValue("√");
			if(records.get(10) != null && AIMUtil.isNumeric(records.get(10))) {
				Calendar cal = Calendar.getInstance();
				cal.setTimeInMillis(Long.parseLong(records.get(10)));
				getCell(row, 26).setCellValue(cal.getTime());
			}
			getCell(row, 27).setCellValue(records.get(11));
			count0++;
			row0Index++;
		}
	}
	
	private void setRecords1(Sheet sheet, Dealer dealer, List<String> records) {
		if(!records.isEmpty()) {
			Row row = sheet.getRow(row1Index);
			getCell(row, 0).setCellValue(count1);
			getCell(row, 1).setCellValue(dealer.getRssc());
			getCell(row, 2).setCellValue(dealer.getProvince());
			getCell(row, 3).setCellValue(dealer.getCity());
			getCell(row, 4).setCellValue(dealer.getNetCode());
			if(customerCodes.get(dealer) != null) {
				getCell(row, 5).setCellValue(customerCodes.get(dealer)); // 开票代码
			}
			getCell(row, 6).setCellValue(dealer.getName());
			getCell(row, 7).setCellValue(dealer.getShortName());
			getCell(row, 8).setCellValue(records.get(0)); // 开单日期
			getCell(row, 9).setCellValue(records.get(1)); // VIN码
			getCell(row, 10).setCellValue(records.get(2)); // 发动机号码
			getCell(row, 11).setCellValue(records.get(3)); // 车辆代码
			getCell(row, 12).setCellValue(records.get(4)); // 车辆名称
			getCell(row, 13).setCellValue(records.get(7)); // 车型大类
			getCell(row, 14).setCellValue(records.get(5)); // 颜色代码
			getCell(row, 15).setCellValue(records.get(6)); // 颜色名称
			getCell(row, 17).setCellValue("账面-未确认在库"); // 现场检查状态
			getCell(row, 18).setCellValue(records.get(8)); // 检查结果明细
			getCell(row, 21).setCellValue(records.get(9)); // 具体原因说明
			getCell(row, 22).setCellValue(records.get(10)); // 实车出库时间
			getCell(row, 24).setCellValue(records.get(11)); // OMD零售信息截屏有无车辆零售信息
			getCell(row, 25).setCellValue(records.get(12)); // 提供的证明文件-证明材料
			count1++;
			row1Index++;
		}
	}
	
	private void setRecords2(Sheet sheet, Dealer dealer, List<String> records) {
		if(!records.isEmpty()) {
			Row row = sheet.getRow(row2Index);
			getCell(row, 0).setCellValue(count2);
			getCell(row, 1).setCellValue(dealer.getRssc());
			getCell(row, 2).setCellValue(dealer.getProvince());
			getCell(row, 3).setCellValue(dealer.getCity());
			getCell(row, 4).setCellValue(dealer.getNetCode());
			if(customerCodes.get(dealer) != null) {
				getCell(row, 5).setCellValue(customerCodes.get(dealer)); // 开票代码
			}
			getCell(row, 6).setCellValue(dealer.getName());
			getCell(row, 7).setCellValue(dealer.getShortName());
			getCell(row, 9).setCellValue(records.get(1)); // VIN码
			if(records.get(2) != null) {
				getCell(row, 10).setCellValue(records.get(2)); // 发动机号码
			}
			getCell(row, 11).setCellValue(records.get(3)); // 车辆代码
			getCell(row, 12).setCellValue(records.get(9)); // 车辆名称
			getCell(row, 13).setCellValue(records.get(12)); // 车型大类
			getCell(row, 14).setCellValue(records.get(10)); // 颜色代码
			getCell(row, 15).setCellValue(records.get(11)); // 颜色名称
			getCell(row, 17).setCellValue("已零售出库未开发票车辆-未确认在库"); // 现场检查状态
			getCell(row, 18).setCellValue(records.get(5)); // 检查结果明细
			getCell(row, 21).setCellValue(records.get(6)); // 具体原因说明
			getCell(row, 22).setCellValue(records.get(7)); // 实车出库时间
			getCell(row, 25).setCellValue(records.get(8));
			if(records.get(14) != null && AIMUtil.isNumeric(records.get(14))) {
				Calendar cal = Calendar.getInstance();
				cal.setTimeInMillis(Long.parseLong(records.get(14)));
				getCell(row, 26).setCellValue(cal.getTime());
			}
			count2++;
			row2Index++;
		}
	}
	
	private void createVSFile() throws IOException {
		File sourceFile = new File(AIMUtil.getSystemConfig("vin.statistic.path") +
				File.separator + year + "-Q" + quarter + "-明细表.xls");
		FileOutputStream out = null;
		try {
			out = new FileOutputStream(sourceFile);
			workbook.write(out);
			out.flush();
			//workbook.
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (out != null) {
				try {
					out.close();
				} catch (IOException e) {
					// do nothing
				}
			}
		}
		String destName = year + "-Q" + quarter + "-明细表";
		FileUtils.copyFile(sourceFile, new File(AIMUtil.getSystemConfig("execl.temp.path") + File.separator + destName + ".xls"));
	}
}